package com.tcfvic.server;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

import com.tcfvic.client.PersonService;
import com.tcfvic.client.pojos.Address;
import com.tcfvic.client.pojos.Course;
import com.tcfvic.client.pojos.Courselevel;
import com.tcfvic.client.pojos.Employmentdetails;
import com.tcfvic.client.pojos.Employmentstatus;
import com.tcfvic.client.pojos.Expense;
import com.tcfvic.client.pojos.Fundingsource;
import com.tcfvic.client.pojos.Industry;
import com.tcfvic.client.pojos.Jsp;
import com.tcfvic.client.pojos.Logs;
import com.tcfvic.client.pojos.Person;
import com.tcfvic.client.pojos.Personaddress;
import com.tcfvic.client.pojos.PersonaddressId;
import com.tcfvic.client.pojos.Personcourse;
import com.tcfvic.client.pojos.Personcoursefundingsource;
import com.tcfvic.client.pojos.Personfundingsource;
import com.tcfvic.client.pojos.Personjsp;
import com.tcfvic.client.pojos.Qualification;
import com.tcfvic.client.pojos.Rto;
import com.tcfvic.client.pojos.Workercompany;
import com.tcfvic.client.pojosInternal.AvailableFunding;
import com.tcfvic.server.persistance.HibernateUtil;

public class PersonServiceImpl extends AbstractService implements PersonService {
	private static final Logger logger = Logger
			.getLogger(PersonServiceImpl.class);
	private static final long serialVersionUID = 2574119698402240940L;

	static {
		Thread t = new Thread(new Runnable() {
			public void run() {
				while (true) {
					try {
						logger.info("Running keep alive query");
						Session session = HibernateUtil.getSession();
						Transaction tr = session.beginTransaction();
						session.createQuery("from Person").list();
						tr.commit();
						session.close();
					} catch (Exception e) {
						e.printStackTrace();
					}
					try {
						Thread.sleep(1000 * 60 * 10);
					} catch (InterruptedException e) {
					}
				}
			}
		});
		t.setDaemon(true);
		t.start();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Person> getAllPersonDetails() {
		List<Person> dtos = null;

		try {
			Session session = HibernateUtil.getSession();
			session.beginTransaction();
			Criteria crit = session.createCriteria(Person.class);
			crit.add(Restrictions.or(Restrictions.eq("display", ""),
					Restrictions.isNull("display")));
			crit.addOrder(Order.asc("surname"));
			crit.addOrder(Order.asc("firstname"));
			dtos = crit.list();
			// dtos = new ArrayList<Person>(
			// session
			// .createQuery(
			// "where display is null or display != 'N' order by surname, firstname")
			// .list());
			session.getTransaction().commit();
		} catch (Exception e) {
			logger
					.error("Error captured in PersonDetailsImpl.getAllPersonDetails: "
							+ e);
		}

		return dtos;
	}

	@Override
	public Person savePersonDetails(Person details) throws Exception {
		Session session = null;
		Address address = null;
		String hql = "";
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();

			// save the address
			if (details.getPersonaddresses() != null
					&& details.getPersonaddresses().size() == 1) {
				Set<Personaddress> personAddresses = details
						.getPersonaddresses();
				for (Personaddress personAddress : personAddresses) {
					if (personAddress.getAddress() != null) {
						address = personAddress.getAddress();
						if (address.getId() > 0) {
							session.saveOrUpdate(address);
						} else {
							address.setId((Integer) session.save(address));
						}
					}
				}
			}

			// save or update the person details
			details.setPersonaddresses(null);
			if (details.getId() > 0) {
				session.saveOrUpdate(details);
			} else {
				details.setId((Integer) session.save(details));
			}

			// save the person -> address link if address is filled in
			if (address != null) {
				Personaddress personAddress = new Personaddress();
				personAddress.setId(new PersonaddressId(details.getId(),
						address.getId()));
				personAddress.setDateofarrival(new Date());
				session.saveOrUpdate(personAddress);
			}

			// update the person funding sources table
			hql = "delete from Personfundingsource where Person_id = "
					+ details.getId();
			session.createQuery(hql).executeUpdate();
			if (details.getPersonfundingsources() != null) {
				for (Personfundingsource source : details
						.getPersonfundingsources()) {
					source.getId().setPersonId(details.getId());
					session.saveOrUpdate(source);
				}
			}

			// update the training table
			hql = "delete from Personcoursefundingsource where PersonCourse_Person_id = "
					+ details.getId();
			session.createQuery(hql).executeUpdate();
			hql = "delete from Personcourse where Person_id = "
					+ details.getId();
			session.createQuery(hql).executeUpdate();
			if (details.getPersoncourses() != null) {
				List<String> ids = new ArrayList<String>();
				for (Personcourse source : details.getPersoncourses()) {
					try {
						source.getId().setPersonId(details.getId());
						int random = (int) System.currentTimeMillis() / 2;
						source.getId().setId(random);
						session.saveOrUpdate(source);
						ids.add(source.getId().getCourseId() + " "
								+ source.getId().getPersonId());
						for (Personcoursefundingsource pcfs : source
								.getPersoncoursefundingsources()) {
							pcfs.getId().setPersonCoursePersonId(
									details.getId());
							pcfs.getId().setPersonCourseId(
									source.getId().getId());
							pcfs.getId().setPersonCourseCourseId(
									source.getId().getCourseId());
							session.saveOrUpdate(pcfs);
						}
					} catch (Exception e) {
						logger.error("Error with import of ("
								+ details.getFirstname() + " "
								+ details.getSurname()
								+ "), couldn't insert course "
								+ source.getCourse().getName());
					}
				}
			}

			// update the employment details table
			hql = "delete from Employmentdetails where Person_id = "
					+ details.getId();
			session.createQuery(hql).executeUpdate();
			if (details.getEmploymentdetailses() != null) {
				for (Employmentdetails source : details
						.getEmploymentdetailses()) {
					source.getId().setPersonId(details.getId());
					session.saveOrUpdate(source);
				}
			}

			// update the employment details table
			hql = "delete from Personjsp where Person_id = " + details.getId();
			session.createQuery(hql).executeUpdate();
			if (details.getPersonjsps() != null) {
				for (Personjsp source : details.getPersonjsps()) {
					source.getId().setPersonId(details.getId());
					session.saveOrUpdate(source);
				}
			}

			session.getTransaction().commit();
		} catch (Exception e) {
			logger
					.error("Error captured in PersonDetailsImpl.savePersonDetails: "
							+ e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
		return serialize(details);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Person getPersonDetails(int id) {
		Person dto = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			Criteria crit = session.createCriteria(Person.class);
			crit.add(Restrictions.eq("id", id)); // Like condition
			List<Person> personDetails = crit.list();
			if (personDetails.size() == 0 || personDetails.size() > 1
					|| personDetails.get(0) == null) {
				throw new Exception(
						"Either no person records were found or more then 1 with ID: "
								+ id);
			}
			for (Expense expense : personDetails.get(0).getExpenses()) {
				expense.setPerson(null);
				if (expense.getFundingsource() != null) {
					expense.getFundingsource().setExpenses(null);
					expense.getFundingsource().setPersoncoursefundingsources(
							null);
					expense.getFundingsource().setPersonfundingsources(null);
				}
			}
			for (Personaddress address : personDetails.get(0)
					.getPersonaddresses()) {
				address.setPerson(null);
				if (address.getAddress() != null) {
					address.getAddress().setPersonaddresses(null);
				}
			}
			for (Personjsp jsp : personDetails.get(0).getPersonjsps()) {
				jsp.setPerson(null);
				if (jsp.getJsp() != null) {
					jsp.getJsp().setPersonjsps(null);
				}
			}
			for (Personfundingsource pfs : personDetails.get(0)
					.getPersonfundingsources()) {
				pfs.setPerson(null);
				if (pfs.getFundingsource() != null) {
					pfs.getFundingsource().setPersonfundingsources(null);
					pfs.getFundingsource().setPersoncoursefundingsources(null);
					pfs.getFundingsource().setExpenses(null);
				}
			}
			for (Personcourse pfs : personDetails.get(0).getPersoncourses()) {
				pfs.setPerson(null);
				pfs.setPersoncoursefundingsources(null);
				if (pfs.getCourse() != null) {
					pfs.getCourse().setPersoncourses(null);
					pfs.getCourse().setRto(null);
					pfs.getCourse().setCourselevel(null);
				}
				if (pfs.getQualification() != null) {
					pfs.getQualification().setPersoncourses(null);
				}
				if (pfs.getRto() != null) {
					pfs.getRto().setCourses(null);
					pfs.getRto().setPersoncourses(null);
				}
			}
			for (Employmentdetails ed : personDetails.get(0)
					.getEmploymentdetailses()) {
				ed.setPerson(null);
				ed.setPerson(null);
				if (ed.getWorkercompany() != null) {
					ed.getWorkercompany().setEmploymentdetailses(null);
				}
			}
			dto = serialize(personDetails.get(0));
			session.getTransaction().commit();
		} catch (Throwable e) {
			logger
					.info("Error captured in PersonDetailsImpl.getPersonDetails: "
							+ e);
		}
		return dto;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Workercompany> getAllCompanies() {
		List<Workercompany> companies = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			companies = new ArrayList<Workercompany>(session.createQuery(
					"from Workercompany").list());
			for (Workercompany wc : companies) {
				wc.setEmploymentdetailses(null);
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			System.err
					.println("Error captured in PersonDetailsImpl.getAllCompanies: "
							+ e);
		}
		return companies;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Workercompany getCompanyDetails(int id) {
		Workercompany dto = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			Criteria crit = session.createCriteria(Workercompany.class);
			crit.add(Restrictions.eq("id", id)); // Like condition

			List<Workercompany> companies = crit.list();
			if (companies.size() == 0 || companies.size() > 1
					|| companies.get(0) == null) {
				throw new Exception(
						"Either no company records were found or more then 1 with ID: "
								+ id);
			}
			companies.get(0).setEmploymentdetailses(null);
			dto = serialize(companies.get(0));
			session.getTransaction().commit();
		} catch (Exception e) {
			System.err
					.println("Error captured in PersonDetailsImpl.getCompanyDetails: "
							+ e);
		}
		return dto;
	}

	@Override
	public void saveCompanyDetails(Workercompany company) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			session.saveOrUpdate(company);
			session.getTransaction().commit();
		} catch (Exception e) {
			System.err
					.println("Error captured in PersonDetailsImpl.savePersonDetails: "
							+ e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
	}

	public void writeLog(Logs log) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			session.save(log);
			session.getTransaction().commit();
		} catch (Exception e) {
			System.err.println("Error captured in PersonDetailsImpl.writeLog: "
					+ e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Fundingsource> getAllFundingSources() {
		List<Fundingsource> fundingSources = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			fundingSources = new ArrayList<Fundingsource>(
					session
							.createQuery(
									"from Fundingsource where display is null or display != 'N'")
							.list());
			for (Fundingsource fs : fundingSources) {
				fs.setPersonfundingsources(null);
				fs.setPersoncoursefundingsources(null);
				fs.setExpenses(null);
			}
			fundingSources = serialize(fundingSources);
			session.getTransaction().commit();
		} catch (Exception e) {
			System.err
					.println("Error captured in PersonDetailsImpl.getAllFundingSources: "
							+ e);
		}
		return fundingSources;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Fundingsource getFundingSourceDetails(int id) {
		Fundingsource dto = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			Criteria crit = session.createCriteria(Fundingsource.class);
			crit.add(Restrictions.eq("id", id)); // Like condition

			List<Fundingsource> fundingSources = crit.list();
			if (fundingSources.size() == 0 || fundingSources.size() > 1
					|| fundingSources.get(0) == null) {
				throw new Exception(
						"Either no funding source records were found or more then 1 with ID: "
								+ id);
			}
			fundingSources.get(0).setPersonfundingsources(null);
			fundingSources.get(0).setPersoncoursefundingsources(null);
			fundingSources.get(0).setExpenses(null);
			dto = serialize(fundingSources.get(0));
			session.getTransaction().commit();
		} catch (Exception e) {
			System.err.println("Error captured in getFundingSourceDetails: "
					+ e);
		}
		return dto;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Fundingsource> getPersonFundingSources(int personId) {
		List<Fundingsource> sources = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			Criteria crit = session.createCriteria(Fundingsource.class);
			crit.add(Restrictions.or(Restrictions.eq("display", ""),
					Restrictions.isNull("display")));
			sources = crit.list();

			for (Fundingsource source : sources) {
				source.setExpenses(null);
				source.setPersoncoursefundingsources(null);
				source.setPersonfundingsources(null);
			}
			sources = serialize(sources);
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in getPersonFundingSources: " + e);
		}
		return sources;
	}

	// @SuppressWarnings("unchecked")
	// @Override
	// public List<Personcourse> getPersonCoursesByFundingID(int fundingId) {
	// List<Personcourse> courses = null;
	// try {
	// Session session = HibernateUtil.getSession();
	// 
	// session.beginTransaction();
	// String hql = "from Personcourse where FundingSource_id= "
	// + fundingId;
	// courses = session.createQuery(hql).list();
	// for (Personcourse source : courses) {
	// source.setPerson(null);
	// source.getFundingsource().setPersoncourses(null);
	// source.getFundingsource().setPersonfundingsources(null);
	// }
	// courses = serialize(courses);
	// session.getTransaction().commit();
	// } catch (Exception e) {
	// System.err
	// .println("Error captured in getPersonFundingSourcesByFundingID: " + e);
	// }
	// return courses;
	// }

	@Override
	public void saveFundingSourceDetails(Fundingsource fundingSource)
			throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			session.saveOrUpdate(fundingSource);
			session.getTransaction().commit();
		} catch (Exception e) {
			System.err
					.println("Error captured in PersonDetailsImpl.saveFundingSourceDetails: "
							+ e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Course> getAllTraining() {
		List<Course> courses = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			courses = new ArrayList<Course>(session.createQuery(
					"from Course order by name").list());
			for (Course c : courses) {
				c.setPersoncourses(null);
			}
			courses = serialize(courses);
			session.getTransaction().commit();
		} catch (Exception e) {
			System.err
					.println("Error captured in PersonDetailsImpl.getAllTraining: "
							+ e);
		}
		return courses;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Course getTrainingDetails(int id) {
		Course dto = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			Criteria crit = session.createCriteria(Course.class);
			crit.add(Restrictions.eq("id", id)); // Like condition

			List<Course> courses = crit.list();
			if (courses.size() == 0 || courses.size() > 1
					|| courses.get(0) == null) {
				throw new Exception(
						"Either no courses were found or more then 1 with ID: "
								+ id);
			}
			courses.get(0).setPersoncourses(null);
			if (courses.get(0).getCourselevel() != null) {
				courses.get(0).getCourselevel().setCourses(null);
			}
			if (courses.get(0).getRto() != null) {
				courses.get(0).getRto().setCourses(null);
			}
			dto = serialize(courses.get(0));
			session.getTransaction().commit();
		} catch (Exception e) {
			System.err.println("Error captured in getTrainingDetails: " + e);
		}
		return dto;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Personcourse> getPersonCourseDetails(int personId) {
		List<Personcourse> courses = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			String hql = "from Personcourse where Person_id = " + personId;
			courses = session.createQuery(hql).list();

			for (Personcourse course : courses) {
				course.setPerson(null);
				if (course.getQualification() != null) {
					course.getQualification().setPersoncourses(null);
				}
				if (course.getRto() != null) {
					course.getRto().setCourses(null);
					course.getRto().setPersoncourses(null);
				}
				course.getCourse().setPersoncourses(null);
				course.setPersoncoursefundingsources(new HashSet(
						getPersonCourseFundingSource(personId, course.getId()
								.getCourseId())));
				course.getCourse().setCourselevel(null);
				course.getCourse().setRto(null);
				if (course.getRto() != null) {
					course.getRto().setPersoncourses(null);
					course.getRto().setCourses(null);
				}
			}
			courses = serialize(courses);
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in getPersonCourseDetails: " + e);
		}
		return courses;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Personcoursefundingsource> getPersonCourseFundingSource(
			int personId) {
		List<Personcoursefundingsource> courses = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			String hql = "from Personcoursefundingsource where PersonCourse_Person_id = "
					+ personId;
			courses = session.createQuery(hql).list();

			for (Personcoursefundingsource course : courses) {
				course.getFundingsource().setPersoncoursefundingsources(null);
				course.getPersoncourse().setPersoncoursefundingsources(null);
			}
			courses = serialize(courses);
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in getPersonCourseDetails: " + e);
		}
		return courses;
	}

	@SuppressWarnings("unchecked")
	public List<Personcoursefundingsource> getPersonCourseFundingSource(
			int personId, int courseId) {
		List<Personcoursefundingsource> courses = null;
		try {
			Session session = HibernateUtil.getSession();
			session.beginTransaction();
			String hql = "from Personcoursefundingsource "
					+ "where PersonCourse_Person_id = " + personId
					+ " and PersonCourse_Course_id = " + courseId;
			courses = session.createQuery(hql).list();

			for (Personcoursefundingsource course : courses) {
				course.setFundingsource(null);
				course.setPersoncourse(null);
			}
			courses = serialize(courses);
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in getPersonCourseDetails: " + e);
		}
		return courses;
	}

	@Override
	public void saveTrainingDetails(Course course) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			session.saveOrUpdate(course);
			session.getTransaction().commit();
		} catch (Exception e) {
			System.err
					.println("Error captured in PersonDetailsImpl.saveTrainingDetails: "
							+ e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Expense> getAllExpenses(Integer personId) {
		List<Expense> expenses = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			expenses = new ArrayList<Expense>(session.createQuery(
					"from Expense where Person_id = " + personId).list());
			for (Expense c : expenses) {
				c.setPerson(null);
				if (c.getFundingsource() != null) {
					c.getFundingsource().setPersoncoursefundingsources(null);
					c.getFundingsource().setPersonfundingsources(null);
					c.getFundingsource().setExpenses(null);
				}
			}
			expenses = serialize(expenses);
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in PersonDetailsImpl.getAllExpenses",
					e);
		}
		return expenses;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Expense getExpense(int expenseId) {
		List<Expense> expenses = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			expenses = new ArrayList<Expense>(session.createQuery(
					"from Expense where id = " + expenseId).list());

			if (expenses.size() == 0 || expenses.get(0) == null) {
				throw new Exception(
						"Either no expense records were found or more then 1 with ID: "
								+ expenseId);
			}
			for (Expense c : expenses) {
				c.setPerson(null);
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in PersonDetailsImpl.getExpense", e);
		}
		return serialize(expenses.get(0));
	}

	@Override
	public void saveExpense(Expense expense) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			session.saveOrUpdate(expense);
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in PersonDetailsImpl.saveExpenses", e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
	}

	@Override
	public void saveExpenses(List<Expense> expenses, boolean deleteFirst)
			throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			if (expenses.size() > 0) {
				if (deleteFirst) {
					try {
						// attemp to delete any existing records
						String hql = "delete from Expense where Person_id = "
								+ ((Expense) expenses.get(0)).getId()
										.getPersonId();
						session.createQuery(hql).executeUpdate();
					} catch (Exception e) {
						// do nothing
					}
				}
				// update the person funding sources table
				for (Expense expense : expenses) {
					session.save(expense);
				}
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in PersonDetailsImpl.saveExpenses", e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Courselevel> getAllCourseLevels() {
		List<Courselevel> courseLevels = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			courseLevels = new ArrayList<Courselevel>(session.createQuery(
					"from Courselevel").list());
			for (Courselevel cl : courseLevels) {
				cl.setCourses(null);
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error(
					"Error captured in PersonDetailsImpl.getAllCourseLevel", e);
		}
		return serialize(courseLevels);
	}

	@Override
	public void saveCourseLevel(Courselevel courseLevel) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			session.saveOrUpdate(courseLevel);
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in PersonDetailsImpl.saveCourseLevel",
					e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Rto> getAllRTOs() {
		List<Rto> rtos = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();

			rtos = new ArrayList<Rto>(session.createQuery(
					"from Rto order by name").list());
			for (Rto rto : rtos) {
				rto.setCourses(null);
				rto.setPersoncourses(null);
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in PersonServiceImpl.getAllRTOs", e);
		}
		return serialize(rtos);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Rto getRTO(int id) {
		List<Rto> rtos = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			rtos = new ArrayList<Rto>(session.createQuery(
					"from Rto where id = " + id).list());

			if (rtos.size() == 0 || rtos.get(0) == null) {
				throw new Exception(
						"Either no RTO records were found or more then 1 with ID: "
								+ id);
			}
			for (Rto rto : rtos) {
				rto.setCourses(null);
				rto.setPersoncourses(null);
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in PersonDetailsImpl.getExpense", e);
		}
		return serialize(rtos.get(0));
	}

	@Override
	public void saveRTO(Rto rto) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			session.saveOrUpdate(rto);
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in PersonDetailsImpl.saveRTO", e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
	}

	public static void main(String... args) throws Exception {
		PersonServiceImpl p = new PersonServiceImpl();
		
		Calendar c = Calendar.getInstance();
		c.setTime(new Date());
		c.add(Calendar.DAY_OF_MONTH, 0);

		List<Logs> l = p.getAllLogs(c.getTime());
		for (Logs log : l) {
			System.out.println(log.getText());
		}
		

	}

	@SuppressWarnings("unchecked")
	public List<Expense> getAvailableAmounts2(int personId) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();

			ArrayList<Expense> expenseList = new ArrayList<Expense>(session
					.createQuery("from Expense where Person_id = " + personId)
					.list());
			return expenseList;

		} catch (Exception e) {

		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public List<AvailableFunding> getAvailableAmounts(int personId)
			throws Exception {
		Session session = null;
		List<AvailableFunding> availableFundingList = new ArrayList<AvailableFunding>();
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();

			ArrayList<Fundingsource> pfsList = new ArrayList<Fundingsource>(
					session
							.createQuery(
									"from Fundingsource where display is null or display != 'N'")
							.list());

			ArrayList<Expense> expenseList = new ArrayList<Expense>(
					session
							.createQuery(
									"from Expense where Person_id = "
											+ personId
											+ " and (units = 'Dollars' or units = '' or units is null)")
							.list());
			Map<Integer, Double> expenseMap = new HashMap<Integer, Double>();
			for (Expense expense : expenseList) {
				if (expense.getFundingsource() != null
						&& expense.getAmount() != null) {
					Double expenseAlreadyStored = expenseMap.get(expense
							.getFundingsource().getId());
					if (expenseAlreadyStored == null) {
						expenseMap.put(expense.getFundingsource().getId(),
								expense.getAmount());
					} else {
						if (expense != null) {
							expenseMap.put(expense.getFundingsource().getId(),
									expenseAlreadyStored + expense.getAmount());
						}
					}
				}
			}

			if (pfsList != null) {

				for (Fundingsource pfs : pfsList) {
					Double amount = 0D;
					if (expenseMap.get(pfs.getId()) != null) {
						amount += expenseMap.get(pfs.getId());
					}
					availableFundingList.add(new AvailableFunding(pfs
							.getSourceName(), amount));
				}
			}

			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error(
					"Error captured in PersonDetailsImpl.getAvailableAmounts",
					e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
		return serialize(availableFundingList);
	}

	// @SuppressWarnings("unchecked")
	// public List<AvailableFunding> getAvailableAmounts(int personId)
	// throws Exception {
	// Session session = null;
	// List<AvailableFunding> availableFundingList = new
	// ArrayList<AvailableFunding>();
	// try {
	// session = HibernateUtil.getSession();
	// session.beginTransaction();
	//
	// ArrayList<Personfundingsource> pfsList = new
	// ArrayList<Personfundingsource>(
	// session.createQuery(
	// "from Personfundingsource where Person_id = "
	// + personId).list());
	//
	// ArrayList<Expense> expenseList = new ArrayList<Expense>(session
	// .createQuery(
	// "from Expense where Person_id = " + personId
	// + " and units = 'Dollars'").list());
	// Map<Integer, Double> expenseMap = new HashMap<Integer, Double>();
	// for (Expense expense : expenseList) {
	// if (expense.getFundingsource() != null
	// && expense.getAmount() != null) {
	// Double expenseAlreadyStored = expenseMap.get(expense
	// .getFundingsource().getId());
	// if (expenseAlreadyStored == null) {
	// expenseMap.put(expense.getFundingsource().getId(),
	// expense.getAmount());
	// } else {
	// if (expense != null) {
	// expenseMap.put(expense.getFundingsource().getId(),
	// expenseAlreadyStored + expense.getAmount());
	// }
	// }
	// }
	// }
	//
	// if (pfsList != null) {
	//
	// for (Personfundingsource pfs : pfsList) {
	// Double amountRemaining = 0D;
	// if (pfs.getFundingsource().getInitialamount() != null) {
	// amountRemaining = pfs.getFundingsource()
	// .getInitialamount();
	// }
	// if (expenseMap.get(pfs.getFundingsource().getId()) != null) {
	// amountRemaining -= expenseMap.get(pfs
	// .getFundingsource().getId());
	// }
	// availableFundingList.add(new AvailableFunding(pfs
	// .getFundingsource().getSourceName(),
	// amountRemaining));
	// }
	// }
	//
	// session.getTransaction().commit();
	// } catch (Exception e) {
	// logger.error(
	// "Error captured in PersonDetailsImpl.getAvailableAmounts",
	// e);
	// if (session != null) {
	// session.getTransaction().rollback();
	// }
	// throw e;
	// }
	// return serialize(availableFundingList);
	// }

	// @SuppressWarnings("unchecked")
	// public List<AvailableFunding> getAvailableAmounts(int personId)
	// throws Exception {
	// Session session = null;
	// List<AvailableFunding> availableFundingList = new
	// ArrayList<AvailableFunding>();
	// try {
	// session = HibernateUtil.getSession();
	// session.beginTransaction();
	//
	// ArrayList<Personfundingsource> pfsList = new
	// ArrayList<Personfundingsource>(
	// session.createQuery(
	// "from Personfundingsource where Person_id = "
	// + personId).list());
	//
	// if (pfsList != null) {
	//
	// for (Personfundingsource pfs : pfsList) {
	// Double total = 0.0;
	// Double totalDays = 0.0;
	// Double totalHours = 0.0;
	// if (pfs == null) {
	// continue;
	// }
	// List<Personcoursefundingsource> pcfsList = new
	// ArrayList<Personcoursefundingsource>(
	// session.createQuery(
	// "from Personcoursefundingsource where PersonCourse_Person_id = "
	// + personId
	// + " and FundingSource_id = "
	// + pfs.getId().getFundingSourceId())
	// .list());
	// for (Personcoursefundingsource pcfs : pcfsList) {
	// if (pcfs.getCost() != null) {
	// total += pcfs.getCost();
	// }
	// if (pcfs.getTrainingdaysclaimed() != null) {
	// totalDays += pcfs.getTrainingdaysclaimed();
	// }
	// if (pcfs.getTraininghourclaimed() != null) {
	// totalHours += pcfs.getTraininghourclaimed();
	// }
	// }
	// if (pfs.getFundingsource().getInitialamount() != null) {
	// total = pfs.getFundingsource().getInitialamount()
	// - total;
	// }
	//
	// availableFundingList.add(new AvailableFunding(pfs
	// .getFundingsource().getSourceName(), total));
	//
	// if (Constants.PACIFIC_BRANDS.equals(pfs.getFundingsource()
	// .getSourceName())) {
	// if (pfs.getFundingsource().getDays() != null) {
	// totalDays = pfs.getFundingsource().getDays()
	// - totalDays;
	// }
	// if (pfs.getFundingsource().getHours() != null) {
	// totalHours = pfs.getFundingsource().getHours()
	// - totalHours;
	// }
	// availableFundingList.add(new AvailableFunding(pfs
	// .getFundingsource().getSourceName()
	// + " Days", totalDays));
	// availableFundingList.add(new AvailableFunding(pfs
	// .getFundingsource().getSourceName()
	// + " Hours", totalHours));
	// }
	// }
	// }
	//
	// session.getTransaction().commit();
	// } catch (Exception e) {
	// logger.error(
	// "Error captured in PersonDetailsImpl.getAvailableAmounts",
	// e);
	// if (session != null) {
	// session.getTransaction().rollback();
	// }
	// throw e;
	// }
	// return serialize(availableFundingList);
	// }

	@SuppressWarnings("unchecked")
	@Override
	public List<Jsp> getAllJSPs() {
		List<Jsp> jsps = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			jsps = new ArrayList<Jsp>(session.createQuery("from Jsp order by name").list());
			for (Jsp jsp : jsps) {
				jsp.setPersonjsps(null);
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in PersonDetailsImpl.getAllJSPs", e);
		}
		return serialize(jsps);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Jsp getJSPDetails(int id) {
		List<Jsp> jsps = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			jsps = new ArrayList<Jsp>(session.createQuery(
					"from Jsp where id = " + id).list());

			if (jsps.size() == 0 || jsps.get(0) == null) {
				throw new Exception(
						"Either no JSP records were found or more then 1 with ID: "
								+ id);
			}
			for (Jsp jsp : jsps) {
				jsp.setPersonjsps(null);
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in PersonDetailsImpl.getExpense", e);
		}
		return serialize(jsps.get(0));
	}

	@Override
	public void saveJSPDetails(Jsp jsp) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			session.saveOrUpdate(jsp);
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in PersonDetailsImpl.saveJSPDetails",
					e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Industry> getAllIndustries() {
		List<Industry> industries = null;
		try {
			Session session = HibernateUtil.getSession();
			session.beginTransaction();
			industries = new ArrayList<Industry>(session.createQuery(
					"from Industry order by name").list());
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error(
					"Error captured in PersonDetailsImpl.getAllIndustries", e);
		}
		return serialize(industries);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Industry getIndustryDetails(int id) {
		List<Industry> industries = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			industries = new ArrayList<Industry>(session.createQuery(
					"from Industry where id = " + id).list());

			if (industries.size() == 0 || industries.get(0) == null) {
				throw new Exception(
						"Either no JSP records were found or more then 1 with ID: "
								+ id);
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			logger
					.error(
							"Error captured in PersonDetailsImpl.getIndustryDetails",
							e);
		}
		return serialize(industries.get(0));
	}

	@Override
	public void saveIndustryDetails(Industry industry) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			session.saveOrUpdate(industry);
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error(
					"Error captured in PersonDetailsImpl.saveIndustryDetails",
					e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Qualification> getAllQualifications() {
		List<Qualification> qualifications = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			qualifications = new ArrayList<Qualification>(session.createQuery(
					"from Qualification").list());
			for (Qualification qualification : qualifications) {
				qualification.setPersoncourses(null);
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error(
					"Error captured in PersonDetailsImpl.getAllQualifications",
					e);
		}
		return serialize(qualifications);
	}

	@Override
	public void saveQualification(Qualification qualification) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			session.saveOrUpdate(qualification);
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error(
					"Error captured in PersonDetailsImpl.saveQualification", e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAllSuburbs() {
		List<String> suburbs = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			suburbs = new ArrayList<String>(session.createQuery(
					"select distinct suburb from Address").list());
			Collections.sort(suburbs);
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error(
					"Error captured in PersonDetailsImpl.getAllQualifications",
					e);
		}
		return serialize(suburbs);
	}

	@Override
	public void deletePerson(int id) throws Exception {
		Session session = HibernateUtil.getSession();

		Transaction tr = session.beginTransaction();
		Person person = (Person) session.get(Person.class, new Integer(id));
		person.setDisplay("N");
		session.save(person);
		tr.commit();
	}

	@Override
	public void deleteCompany(int id) throws Exception {
		Session session = HibernateUtil.getSession();

		Transaction tr = session.beginTransaction();
		Workercompany wc = (Workercompany) session.get(Workercompany.class,
				new Integer(id));
		wc.setDisplay("N");
		session.save(wc);
		tr.commit();
	}

	@Override
	public void deleteFundingSource(int id) throws Exception {
		Session session = HibernateUtil.getSession();

		Transaction tr = session.beginTransaction();
		Fundingsource fs = (Fundingsource) session.get(Fundingsource.class,
				new Integer(id));
		fs.setDisplay("N");
		session.save(fs);
		tr.commit();
	}

	@Override
	public void deleteCourse(int id) throws Exception {
		Session session = HibernateUtil.getSession();

		Transaction tr = session.beginTransaction();
		Course fs = (Course) session.get(Course.class, new Integer(id));
		fs.setDisplay("N");
		session.save(fs);
		tr.commit();
	}

	@Override
	public void deleteRto(int id) throws Exception {
		Session session = HibernateUtil.getSession();

		Transaction tr = session.beginTransaction();
		Rto fs = (Rto) session.get(Rto.class, new Integer(id));
		fs.setDisplay("N");
		session.save(fs);
		tr.commit();
	}

	@Override
	public void deleteJsp(int id) throws Exception {
		Session session = HibernateUtil.getSession();

		Transaction tr = session.beginTransaction();
		Jsp fs = (Jsp) session.get(Jsp.class, new Integer(id));
		fs.setDisplay("N");
		session.save(fs);
		tr.commit();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Employmentstatus> getEmploymentStatus(int personId) {
		List<Employmentstatus> empStatus = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			String hql = "from Employmentstatus where Person_id = " + personId;
			empStatus = session.createQuery(hql).list();

			for (Employmentstatus status : empStatus) {
				status.setPerson(null);
			}
			empStatus = serialize(empStatus);
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error("Error captured in getEmploymentStatus: " + e);
		}
		return empStatus;
	}

	@Override
	public void saveEmploymentStatus(List<Employmentstatus> empStatusList,
			boolean deleteFirst) throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			if (empStatusList.size() > 0) {
				if (deleteFirst) {
					try {
						// attemp to delete any existing records
						String hql = "delete from Employmentstatus where Person_id = "
								+ ((Employmentstatus) empStatusList.get(0))
										.getId().getPersonId();
						session.createQuery(hql).executeUpdate();
					} catch (Exception e) {
						// do nothing
					}
				}
				// update the person funding sources table
				for (Employmentstatus empStatus : empStatusList) {
					session.save(empStatus);
				}
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error(
					"Error captured in PersonDetailsImpl.saveEmploymentStatus",
					e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
	}

	@Override
	public void savePersonJSP(Personjsp personJSP, boolean deleteFirst)
			throws Exception {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			if (deleteFirst) {
				try {
					// attemp to delete any existing records
					String hql = "delete from Personjsp where Person_id = "
							+ personJSP.getId().getPersonId();
					session.createQuery(hql).executeUpdate();
				} catch (Exception e) {
					// do nothing
				}
			}
			// update the person funding sources table
			session.save(personJSP);
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error(
					"Error captured in PersonDetailsImpl.saveEmploymentStatus",
					e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
	}

	@Override
	public void truncateDatabase() throws Exception {
		Session session = null;
		List<String> tableToDelete = new ArrayList<String>();
		tableToDelete.add("delete from Qualification");
		tableToDelete.add("delete from Courselevel");
		tableToDelete.add("delete from Personaddress");
		tableToDelete.add("delete from Personfundingsource");
		tableToDelete.add("delete from Personcoursefundingsource");
		tableToDelete.add("delete from Personcourse");
		tableToDelete.add("delete from Personjsp");
		tableToDelete.add("delete from Jsp");
		tableToDelete.add("delete from Address");
		tableToDelete.add("delete from Course");
		tableToDelete.add("delete from Employmentdetails");
		tableToDelete.add("delete from Expense");
		tableToDelete.add("delete from Fundingsource");
		tableToDelete.add("delete from Workercompany");
		tableToDelete.add("delete from Rto");
		tableToDelete.add("delete from Employmentstatus");
		tableToDelete.add("delete from Person");
		tableToDelete.add("delete from Industry");

		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			for (String table : tableToDelete) {
				session.createQuery(table).executeUpdate();
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			logger.error(
					"Error captured in PersonDetailsImpl.truncateDatabase", e);
			if (session != null) {
				session.getTransaction().rollback();
			}
			throw e;
		}
	}

	@Override
	public void deleteIndustry(int id) {
		Session session = HibernateUtil.getSession();

		Transaction tr = session.beginTransaction();
		Industry fs = (Industry) session.get(Industry.class, new Integer(id));
		fs.setDisplay("N");
		session.save(fs);
		tr.commit();
	}

	@Override
	public boolean isAdminUser() {
		boolean adminUser = false;
		HttpServletRequest req = getThreadLocalRequest();
		if (req != null) {
			if (req.getRemoteUser() != null
					&& req.getRemoteUser().equalsIgnoreCase("tcfadmin")) {
				adminUser = true;
			}
		}

		return adminUser;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Employmentdetails> getAllPersonEmploymentDetails() {
		List<Employmentdetails> employDet = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			employDet = new ArrayList<Employmentdetails>(session.createQuery(
					"from Employmentdetails").list());
			for (Employmentdetails ed : employDet) {
				ed.setPerson(null);
			}
			employDet = serialize(employDet);
			session.getTransaction().commit();
		} catch (Exception e) {
			System.err
					.println("Error captured in PersonDetailsImpl.getAllFundingSources: "
							+ e);
		}
		return employDet;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Logs> getAllLogs(Date date) {
		List<Logs> logs = null;
		try {
			Session session = HibernateUtil.getSession();

			Calendar c = Calendar.getInstance();
			c.setTime(date);
			c.add(Calendar.DAY_OF_MONTH, -1);
			session.beginTransaction();
			
			Criteria crit = session.createCriteria(Logs.class);
			crit.add(Restrictions.gt("dateandtime", c.getTime()));
			

			logs = new ArrayList<Logs>(crit.list());
			logs = serialize(logs);
			session.getTransaction().commit();
		} catch (Exception e) {
			System.err
					.println("Error captured in PersonDetailsImpl.getAllFundingSources: "
							+ e);
		}
		return logs;
	}
}
